package table;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import table.col.SqlCol;
import table.inter.ICol;
import table.inter.ISqlCol;
import table.node.BaseNode;
import table.node.DataNode;
import table.node.FormulaNode;
import table.tools.NameValues;
import table.tools.TableA2B;

public class SqlTable implements DataTable {
	//----- Base -----
	private String beanId;
	private String description;
	private String dataTableName;
	private List<Col> colList;

	//----- Sql Table -----
	private DataSource ds;
	private String sqlString;
	//------Flag---------
	private boolean viewFlag;
	private boolean modifyFlag;
	private boolean deleteFlag;
	private boolean insertFlag;
	//----- Base -----
	public void setBeanId(String beanId) {
		this.beanId = beanId;
	}
	public String getBeanId() {
		return beanId;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getDescription() {
		return description;
	}
	public void setDataTableName(String dataTableName) {
		this.dataTableName = dataTableName;
	}
	public String getDataTableName() {
		return this.dataTableName;
	}

	public void setColList(List<Col> colList) {
		this.colList = colList;
	}
	public List<Col> getColList() {
		return this.colList;
	}

	//----- Sql Table -----
	public void setDs(DataSource ds) {
		this.ds = ds;
	}
	public Connection getConnection(){
		if(null != ds)
			try {
				return ds.getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		return null;
	}

	public void setSqlString(String sqlString) {
		this.sqlString = sqlString;
	}
	public String getSqlString() {
		return sqlString;
	}
	
	//------Flag---------
	public void setDeleteFlag(boolean deleteFlag) {
		this.deleteFlag = deleteFlag;
	}
	public boolean isDeleteFlag() {
		return deleteFlag;
	}

	public void setModifyFlag(boolean modifyFlag) {
		this.modifyFlag = modifyFlag;
	}
	public boolean isModifyFlag() {
		return modifyFlag;
	}

	public void setViewFlag(boolean viewFlag) {
		this.viewFlag = viewFlag;
	}
	public boolean isViewFlag() {
		return this.viewFlag;
	}

	public void setInsertFlag(boolean insertFlag) {
		this.insertFlag = insertFlag;
	}
	public boolean isInsertFlag() {
		return insertFlag;
	}

	/***************
	 ***** APIs ****
	 **************/
	
	//----- Helpers -----
	private boolean isLocalCol(Col tCol){
		if(tCol != null){
			try{
				ISqlCol lCol =  (ISqlCol) tCol;
				lCol.getExpression();
				return true;
			} catch(Exception e){}
		}
		return false;
	}
	
	private List<ISqlCol> getLocalCols(){
		ArrayList<ISqlCol> cols = new ArrayList<ISqlCol>();
		for(int i=0; i < this.colList.size(); i++){
			Col tCol = this.colList.get(i);
			if(isLocalCol(tCol)){
				cols.add((ISqlCol)tCol);
			}
		}
		return cols;
	}
	
	private List<Col> getOtherCols(){
		ArrayList<Col> cols = null;
		for(int i=0; i < this.colList.size(); i++){
			Col tCol = this.colList.get(i);
			if(!isLocalCol(tCol)){
				if(cols == null) {
					cols = new ArrayList<Col>();
				}
				cols.add(tCol);
			}
		}
		return cols;
	}
	
	private boolean isIdCol(Col tCol){
		if(tCol != null && tCol.getProperties() != null){
			try{
				NameValues p = tCol.getProperties();
				if(p.hasName("id") && this.isLocalCol(tCol)){
					return true;
				}
			} catch(Exception e){}
		}
		return false;
	}

	private List<SqlCol> getIdCols(){
		ArrayList<SqlCol> cols =  new ArrayList<SqlCol>();
		for(int i=0; i < this.colList.size(); i++){
			Col tCol = this.colList.get(i);
			if(isIdCol(tCol)){
				cols.add((SqlCol)tCol);
			}
		}
		return cols;
	}
	
	private boolean isDataIdCol(Col col){
		if(col != null && col.getProperties() != null){
			try{
				NameValues p = col.getProperties();
				if(p.hasName("dataId") && this.isLocalCol(col)){
					return true;
				}
			} catch(Exception e){}
		}
		return false;
	}

	public SqlCol getDataIdCol(){
		try{
		for(int i=0; i < this.colList.size(); i++){
			Col tCol = this.colList.get(i);
			if(isLocalCol(tCol)){
				if(tCol.getProperties().hasName("dataId")) {
					return (SqlCol)tCol;
				}
			}
		}}catch(Exception e){e.printStackTrace();}
		return null;
	}
	public Col getColByName(String colName) {
		Col col = null;
		for(int i=0; i < this.colList.size(); i++){
			Col tCol = this.colList.get(i);
			if(tCol != null && tCol.getName().equalsIgnoreCase(colName)){
				col = tCol;
				break;
			}
		}
		return col;
	}
	
	//----- Data Handles -----

	// ***********************************************
	// *************Set Cols Data*********************
	// ***********************************************
	public int getPageCount(SetDataParamenters p) {
		int n = p.getPageSize();
	 	int a = 0;
		if(n > 0 && p != null){
			List<String[]> queryList = p.getQueryList();
			String queryString = "";
			Iterator<String[]> it = null;
			for(it=queryList.iterator(); it.hasNext();){
				String[] queryPair = (String[]) it.next();
				queryString = queryString + " and " + queryPair[0] + " like '%" + queryPair[1] + "%'";
			}

			try {
				String sqlString = "SELECT count(*) FROM " + this.getSqlString() + " as " + this.getDataTableName()
								+" where ( 1=1 " + queryString + ")";
				ResultSet rs = this.sqlQuery(sqlString);
				if(null != rs && rs.next()){
				 	int t = rs.getInt(1);
					a = t / n;
					if(a * n < t) a++;
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return a;
	}
	
	/*
	 * setData 
	 * input:
	 */
	public boolean setData(SetDataParamenters p) {
		
		try {
			//----- Prepare Statement -----
			ResultSet rs = this.sqlQuery(sqlStringFromParam(p));
			// 1.Set Local Base Data Cols
			this.setColsDataByResultSet(rs);
			// 2.Set Other Cols
			this.setOtherColsDataByCols();
		} catch (Exception e) { 
			e.printStackTrace();
		}
		
		return false;
	}

	public boolean clearData() {
		Iterator<Col> colIt = null; 
		for(colIt= this.getColList().iterator(); colIt.hasNext(); ){
			Col col = colIt.next();
			col.clearData();
		}
		return false;
	}
	
	public boolean setColsDataByCols(List<String> dstColNames,
		List<String> srcColNames) {
		boolean flag = true;
		int srcDataSize = 0;
		Col tCol = null;

		List<SqlCol> srcLocalCols = new ArrayList<SqlCol>();
		List<Col> srcOtherCols = new ArrayList<Col>();

		for(int i=0; srcColNames != null && i < srcColNames.size(); i++){
			tCol = this.getColByName(srcColNames.get(i));
			if(tCol != null){
				if(this.isLocalCol(tCol)){
					srcLocalCols.add((SqlCol)tCol);
				} else {
					srcOtherCols.add(tCol);
				}
			}
			if(srcDataSize <=0){
				srcDataSize = tCol.getDataSize();
			} else if(tCol.getDataSize() != srcDataSize){
				flag = false;
				break;
			}
		}
		
		List<SqlCol> dstLocalCols = new ArrayList<SqlCol>();
		List<Col> dstOtherCols = new ArrayList<Col>();
		for(int i=0; i < dstColNames.size(); i++){
			String dstColName = dstColNames.get(i);
			tCol = this.getColByName(dstColName);
			if(tCol != null){
				if(this.isLocalCol(tCol)){
					dstLocalCols.add((SqlCol)tCol);
				} else {
					dstOtherCols.add(tCol);
				}
			}
		}
		
		if(flag && srcLocalCols.size() > 0 && srcDataSize > 0){
			Iterator<SqlCol> localIt = null;
			String queryString = "";
			ISqlCol tSqlCol = null;
			
			if(srcLocalCols.size() == 1 ){
				tSqlCol = srcLocalCols.get(0);
				queryString = queryString + " " + tSqlCol.getName() + " in ( ";
				if(srcDataSize >= 1){
					queryString = queryString + " '" + tSqlCol.getValue(0) + "' ";
					for(int i=1; i < srcDataSize; i++){
						queryString = queryString + " , '" + tSqlCol.getValue(i) + "' ";
					}
				}
				queryString = queryString + " )";
			} else {//
				for(int i=0; i < srcDataSize; i++){
					if(i < srcDataSize - 1){
						queryString = queryString + " or ( ";
					} else {
						queryString = queryString + " ( ";
					}
					for(localIt = srcLocalCols.iterator(); localIt.hasNext();){
						tSqlCol = localIt.next();
						try {
							queryString = queryString
								+ tSqlCol.getName() + " = '" 
								+ tSqlCol.getValue(i) + "'";
							if(localIt.hasNext()){
								queryString = queryString + " and "; 
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
					queryString = queryString + " ) ";
				}
			}

			Iterator<ISqlCol> colIt = null; 
			List<ISqlCol> localCols = this.getLocalCols();
			String nameList = "";
			for(colIt = localCols.iterator(); colIt.hasNext();){
				tSqlCol = colIt.next();
				nameList = nameList + ", (" + tSqlCol.getExpression() + ") as "  + tSqlCol.getName() + " ";
			}
			nameList = nameList.substring(1);
			if(nameList.length() > 0){
				String sqlString = "SELECT " + nameList + " FROM " + this.sqlString + " as " + dataTableName 
				+ " where ( 1=1 ) and (" + queryString + ")";
				ResultSet rs = this.sqlQuery(sqlString);
				this.setLocalColsDataByResultSet(rs);
			}
		}
		
		if(dstOtherCols.size() > 0) {
			this.setOtherColsDataByCols();
		}
		
		return false;
	}

	/**
	 * 
	 */
	private void setOtherColsDataByCols() {
		List<Col> otherCols = this.getOtherCols();
		for(int i =0; otherCols != null && i < otherCols.size(); i++){
			otherCols.get(i).setDataByCols(this.getColList());
		}
	}
//	
//	public boolean setIdColDataByResultSet(ResultSet rs){
//		try {
//			while(null != rs && rs.next()){
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		return false;
//	}
	
	public boolean setLocalColsDataByResultSet(ResultSet rs){
		Iterator<ISqlCol> colIt = null; 
		List<ISqlCol> localCols = this.getLocalCols();

		try {
			for(colIt = localCols.iterator(); colIt.hasNext();){
				ISqlCol tCol = colIt.next();
				tCol.clearData();
			}
			while(null != rs && rs.next()){
				for(colIt = localCols.iterator(); colIt.hasNext();){
					ISqlCol tCol = colIt.next();
					tCol.addData(tCol.setNode(rs.getString(tCol.getName())));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	public boolean setColsDataByResultSet(ResultSet rs){
		Iterator<ISqlCol> colIt = null; 
		List<ISqlCol> localCols = this.getLocalCols();

			for(colIt = localCols.iterator(); colIt.hasNext();){
				ISqlCol tCol = colIt.next();
				tCol.clearData();
			}
			try {
				while(null != rs && rs.next()){
					ResultSetMetaData rsmd = rs.getMetaData();   
					int colSize = rsmd.getColumnCount();
					for(int i=1; i <= colSize;i++){
						ISqlCol tCol = (ISqlCol) this.getColByName(rsmd.getColumnLabel(i));
						if(tCol != null){
							tCol.addData(tCol.setNode(rs.getString(tCol.getName())));
						}
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return false;
	}

	
	private String getNameListSql(){
		Iterator<ISqlCol> colIt = null; 
		List<ISqlCol> localCols = this.getLocalCols();
		String nameList = "";
		for(colIt = localCols.iterator(); colIt.hasNext();){
			ISqlCol tCol = colIt.next();
			nameList = nameList + ", (" + tCol.getExpression() + ") as " + tCol.getName() + " ";
		}
		if(nameList.length() > 0){
			nameList = nameList.substring(1);
		}
		return nameList;
	}
	
	private String getDstColSqlFromParam(SetDataParamenters p){
		String  dstNameString = "";
		try{
			List<String> dstNameList = p.getDstColNameList();
			if(dstNameList !=null && dstNameList.size() > 0){
				for(int i=0; i < dstNameList.size(); i++){
					String dstColName = dstNameList.get(i);
					if(this.isLocalCol(this.getColByName(dstColName))){
						ISqlCol tCol = (ISqlCol)this.getColByName(dstColName);
						dstNameString = dstNameString + ", (" + tCol.getExpression() + ") as " + tCol.getName();
					}
				}
				if(dstNameString.length() > 0){
					dstNameString = dstNameString.substring(1);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return dstNameString;
	}
	
	private String getQuerySqlFromParam(SetDataParamenters p){
		String queryString = "";
		Iterator<ISqlCol> colIt = null; 
		try{
			List<ISqlCol> localCols = this.getLocalCols();
			List<String[]> queryList = p.getQueryList();
			if(queryList !=null && queryList.size() > 0){
				for(int i=0; i < queryList.size(); i++){
					String[] queryPair = queryList.get(i);
					for(colIt = localCols.iterator(); colIt.hasNext();){
						ISqlCol tCol = colIt.next();
						if(tCol.getName().equals(queryPair[0])){
							if(queryPair[2] == "="){
								queryString = queryString + " and " + queryPair[0] + " = '" + queryPair[1] + "' ";
							} else {
								queryString = queryString + " and " + queryPair[0] + " like '%" + queryPair[1] + "%' ";
							}
							break;
						}
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return queryString;
	}
	
	private String getSortSqlFromParam(SetDataParamenters p){
		String sortString = "";
		Iterator<ISqlCol> colIt = null; 
		List<ISqlCol> localCols = this.getLocalCols();
		try{
			List<String[]> sortList = p.getSortList();
			if(sortList !=null && sortList.size() > 0){
				for(colIt = localCols.iterator(); colIt.hasNext();){
					ISqlCol tCol = colIt.next();
					for(int i=0; i < sortList.size(); i++){
						String[] sortPair = sortList.get(i);
						if(tCol.getName().equals(sortPair[0])){
							sortString = sortString + ", " + sortPair[0] + " " + sortPair[1] + " ";
							break;
						}
					}
				}
				sortString = " ORDER BY " + sortString.replaceFirst("," , " ");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return sortString;
	}

	/**
	 * 
	 * @param
	 */
	private String sqlStringFromParam(SetDataParamenters p){
		//----- Prepare Statement -----
     	int s = p.getStartPage();//Start Page Num
		int n = p.getPageSize();//Page Size
		String distinctStr = "";
		if(p.isDistinct()){distinctStr = "distinct";}
		String nameList = this.getNameListSql();
		String dstNameList = this.getDstColSqlFromParam(p);
		if(dstNameList.length() <=0){dstNameList = nameList;}
		String queryString = this.getQuerySqlFromParam(p);
		String sortString = this.getSortSqlFromParam(p);
		SqlCol idCol = this.getDataIdCol();
		String sqlString = null;
		if(n == -1){
			sqlString = "SELECT " + distinctStr + " " + dstNameList + " FROM ( SELECT " + nameList + " FROM " + this.sqlString + " as " + dataTableName 
			+" where ( 1=1 " + queryString + ")" + sortString + ") as t ";
		} else {
			sqlString = "SELECT " + distinctStr + " "  + dstNameList + " FROM ( SELECT Top " + n + " " + nameList + " FROM " + this.sqlString + " as " + dataTableName 
			+" where ( 1=1 " + queryString + ") and " 
			+ idCol.getExpression() + " NOT IN (SELECT Top " + s + " (" + idCol.getExpression() + ") as " + idCol.getName() + " FROM " + this.sqlString + " as " + dataTableName 
			+" where ( 1=1 " + queryString + ")  " + sortString + " ) "+ sortString +") as t "
			+ sortString;
		}
		return sqlString;
	}

	public List<Object> getData() {
		return null;
	}
	
	public List<Row> getRowList() {
		List<Row> rowList = new ArrayList<Row>();
		List<Col> allColList = this.getColList();
		if( 0 < allColList.size() && allColList.get(0) != null && allColList.get(0).getDataSize() > 0){
			int dataSize = allColList.get(0).getDataSize();
			for(int i=0; i < dataSize; i++){
				try{
					Row dataRow = new Row();
					for(Iterator<Col> it = allColList.iterator();it.hasNext();){
						Col tCol = it.next(); 
						dataRow.setValueByName(tCol.getName(), tCol.getValue(i));
					}
					rowList.add(dataRow);
				}catch(Exception e){
				}
			}
		}
		return rowList;
	}

	//--------------------------------------------------- 
	//----data Modify(insert,modify,delete) functions----
	//---------------------------------------------------
	/**
	 * @param dataMapList [{colName:colValue}] 
	 * @return successful as true.
	 */
	
	
	public boolean insert(List<Row> rowList) {
     	
		boolean rs = false;
     	
		if((null != rowList && rowList.size() > 0))
		{
			for(int rowIndex = 0; rowIndex < rowList.size(); rowIndex++){
				rs = rs & this.insert(rowList.get(rowIndex));
			}
		}
		return rs;
	}

	
	public boolean insert(Row row) {
     	
		boolean rs = false;
     	
		if((null != row))
		{
			// get id
			List<ISqlCol> localCols = this.getLocalCols();
				
			  try {
				String nameString = "";
				String valueString = "";
				for(Iterator<ISqlCol> colIt = localCols.iterator(); colIt.hasNext();){
					ISqlCol tCol = colIt.next();
					String tName = tCol.getName();
					if(row.hasName(tName)){
						Object tValue = tCol.getColValue(row.getValueByName(tName));
						nameString +=  ", " + tCol.getExpression() + " ";
						valueString += ", '" + tValue.toString() + "' ";;
					}
				}
				if(nameString.length() > 0){
					nameString = nameString.replaceFirst("," , " ");
					valueString = valueString.replaceFirst("," , " ");
					String sqlString = " INSERT INTO  "+ this.getSqlString() 
										+" ( " + nameString + " ) " 
										+ " values ( " + valueString + " ) ";
					
					rs = sqlExecute(sqlString);
				}
			  } catch (Exception e) {
				e.printStackTrace();
			  }
			
		}
		return rs;
	}
	public boolean modify(List<Row> rowList) {
		boolean rs = true;
		if((null != rowList && rowList.size() > 0))
		{
			for(int rowIndex = 0; rowIndex < rowList.size(); rowIndex++){
				rs = rs & this.modify(rowList.get(rowIndex));
			}
		}
		return rs;
	}
	public boolean modify(Row row) {
     	boolean rs = false;

		if((null != row))
		{
			// get id
			SqlCol idCol = this.getDataIdCol();
			List<ISqlCol> localCols = this.getLocalCols();
			String idValue = null;

			if(row.hasName(idCol.getName())){
			  try {
				idValue = (String) idCol.getColValue(row.getValueByName(idCol.getName()));
				idValue = idCol.getName() + " = '" + idValue + "'";
				String nameList = "";
				for(Iterator<ISqlCol> colIt = localCols.iterator(); colIt.hasNext();){
					ISqlCol tCol = colIt.next();
					String tName = tCol.getName();
					if(row.hasName(tName) && !this.isDataIdCol((Col)tCol)){
						Object tValue = tCol.getColValue(row.getValueByName(tName));
						nameList = nameList + ", " + tCol.getExpression() + " = '" + tValue.toString() + "'";
					}
				}
				if(nameList.length() > 0){
					nameList = nameList.replaceFirst("," , " ");
					String sqlString = " UPDATE  "+ this.getSqlString() 
										+" SET " + nameList 
										+ " where " + idValue ;
					
					rs = sqlExecute(sqlString);
				}
			  } catch (Exception e) {
				e.printStackTrace();
			  }
			}
		}
		return rs;
	}

	public boolean delete(List<Row> rowList) {
     	
		boolean rs = false;
     	
		if((null != rowList && rowList.size() > 0))
		{
			for(int rowIndex = 0; rowIndex < rowList.size(); rowIndex++){
				rs = rs & this.delete(rowList.get(rowIndex));
			}
		}
		return rs;
	}
	
	public boolean delete(Row row) {
     	boolean rs = false;
     	
		if((null != row))
		{
			// get id
			SqlCol idCol = this.getDataIdCol();
			String idValue = "";
				
			if(row.hasName(idCol.getName())){
			  try {
				idValue = (String) idCol.getColValue(row.getValueByName(idCol.getName()));
				idValue = idCol.getName() + " = '" + idValue + "'";
				if(idValue.length() > 0){
					String sqlString = " delete from  "+ this.getSqlString() 
										+ " where " + idValue ;
					rs = sqlExecute(sqlString);
				}
			  } catch (Exception e) {
				e.printStackTrace();
			  }
			}
		}
		return rs;
	}

	public DataNode run(HttpServletRequest request) {
		String colAction = request.getParameter("colAction");
		if(colAction != null){
			if( colAction.equalsIgnoreCase("ToExcel")){
				return this.excelData(request);
			} else if(colAction.equalsIgnoreCase("FromExcel")){
				this.importFromTable(request);
			} else if(colAction.equalsIgnoreCase("acQuery")){
				if(request.getParameter("colName") != null){
					String colName = request.getParameter("colName");
					Col col = this.getColByName(colName);
					if(this.isLocalCol(col)){
						if(request.getParameter("colAction").equalsIgnoreCase("acQuery")){
							return this.acQuery(request);
						}
					} else {
						return col.run(request);
					}
				}
			} else if(colAction.equalsIgnoreCase("query")){
				String colName = request.getParameter("colName");
				Col col = this.getColByName(colName);
				if(this.isLocalCol(col)){
					if(request.getParameter("colAction").equalsIgnoreCase("acQuery")){
						return this.acQuery(request);
					}
				} else {
					return col.run(request);
				}
			} else if(colAction.equalsIgnoreCase("import")){
				return this.importFromTable(request);
			} else if(colAction.equalsIgnoreCase("fresh")){
				String colName = request.getParameter("colName");
				Col col = this.getColByName(colName);
				return col.run(request);
			}
		}
		return null;
	}

	private DataNode acQuery(HttpServletRequest request) {
		DataNode result = new BaseNode();
		try{
			Col col = this.getColByName(request.getParameter("colName"));
			String colValue = col.getColValue(request.getParameter("colValue")).toString();
			SetDataParamenters p =new SetDataParamenters(request);
			List<String> dstColNameList = new ArrayList<String>();
			dstColNameList.add(col.getName());
			p.setQuery(col.getName(), colValue);
			p.setDistinct(true);
			p.setDstColNameList(dstColNameList);
			this.setData(p);
			result.setSubNodes(col.getDataList());
			
		}catch(Exception e){
		}
		return result;
	}
	
	private DataNode excelData(HttpServletRequest request){
		DataNode result = new BaseNode();
		result.setNodeValue(this.getDescription());
		try{
			SetDataParamenters p =new SetDataParamenters(request);
			if(p.getPageSize() == 100){
				p.setPageSize(65535);
			}
			this.setData(p);
			List<Col> cols = this.getColList();
			for(int i=0; i < cols.size(); i++){
				Col col = cols.get(i);
				DataNode cNode = new BaseNode();
				cNode.setNodeValue(col.getDescripes());
				cNode.setSubNodes(col.getDataList());
				result.getSubNodes().add(cNode);
			}
		}catch(Exception e){
		}
		return result;
	}
	
	private DataNode importFromTable(HttpServletRequest request){

		try{
	
			TableA2B tA2B = new TableA2B();
			
			
			String ATableSource = request.getParameter("ATableSource");
			DataTable SrcTable = null;
			if(ATableSource.equals("xlsfile")){
				SrcTable = (DataTable) request.getSession().getAttribute("uploadTable");
			} else if(ATableSource.equals("")) {
				
			}
			tA2B.setATable(SrcTable);
			tA2B.setBTable(this);

			List<String> SrcColNames = new ArrayList<String>();
			List<String> DstColNames = new ArrayList<String>();
			for(int i=0; i < this.getColList().size(); i++){
				String srcColName = request.getParameter("SrcColNames"+i);
				String dstColName = request.getParameter("DstColNames"+i);
				if(srcColName!=null && tA2B.getATable().getColByName(srcColName) != null
						&& dstColName!=null && tA2B.getBTable().getColByName(dstColName) != null){
					SrcColNames.add(srcColName);
					DstColNames.add(dstColName);
				} else {
					break;
				}
			}
			
			tA2B.setAColNames(SrcColNames);
			tA2B.setBColNames(DstColNames);
			
			this.insert(tA2B.getBRowList());
		}catch(Exception e){
			
		}	
		
		return null;
	}

	//------------------------------------------------ 
	//--------------sql Tools functions-------------
	//------------------------------------------------
	/**
	 * sql Tools
	 */	
	private boolean sqlExecute(String sqlString){
		Connection conn = this.getConnection();
     	Statement stmt = null;
     	boolean rs = false;
		if(null != conn)
		{
			try {
				stmt =  conn.createStatement();
				if(null != stmt){
					rs = stmt.execute(sqlString);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}
	
	public ResultSet sqlQuery(String sqlString){
		Connection conn = this.getConnection();
     	PreparedStatement stmt = null;
     	ResultSet rs = null;
		
		if(null != conn)
		{
			try {
				stmt = conn.prepareStatement(sqlString);
				rs = stmt.executeQuery();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}
}
